Retrieving Data Using SELECT in MySQL

Course: JustAcademy MySQL Training

Course Demo: Register for MySQL Course Demo

1. Introduction to SELECT in MySQL

The SELECT statement is one of the most important SQL commands used in MySQL. It retrieves data from one or more database tables and displays the results in a result set. Developers, database administrators, analysts, and testers use SELECT to view records, search for specific information, analyze data, and generate reports.

For example, an employee database may contain employee names, IDs, salaries, departments, and joining dates. Using SELECT, you can retrieve all employee details or display only the employee names and salaries you need.

The SELECT statement retrieves information without modifying the existing table records.

2. Learning Objectives

• Understand the purpose and syntax of SELECT.
• Retrieve all records and selected columns from a table.
• Filter records using WHERE.
• Use comparison, logical, and pattern-matching operators.
• Sort records using ORDER BY.
• Limit the number of returned rows.
• Remove duplicate results using DISTINCT.
• Use aliases and calculated expressions.
• Work with NULL values and aggregate functions.
• Practise data retrieval using real-world examples.

3. What Is the SELECT Statement?

The SELECT statement retrieves data stored in database tables. You can select every column, specific columns, calculated values, or results that satisfy particular conditions.

Basic Syntax

SELECT column_name
FROM table_name;

Example

SELECT employee_name
FROM employees;

This query retrieves the employee_name column from the employees table.

General Syntax with a Condition

SELECT column1, column2
FROM table_name
WHERE condition;

The WHERE clause is optional. When it is omitted, SELECT generally returns all rows from the specified table, subject to other clauses such as LIMIT.

4. Creating a Sample Database and Table

Before practising SELECT queries, create a database and an employees table.

Step 1: Create a Database

CREATE DATABASE company_db;

Step 2: Select the Database

USE company_db;

Step 3: Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    city VARCHAR(50),
    joining_date DATE
);

Step 4: Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, city, joining_date)
VALUES
(101, 'Amit Sharma', 'IT', 55000.00, 'Mumbai', '2023-01-15'),
(102, 'Priya Patel', 'HR', 45000.00, 'Pune', '2022-06-10'),
(103, 'Rahul Verma', 'IT', 65000.00, 'Mumbai', '2021-03-20'),
(104, 'Neha Singh', 'Finance', 50000.00, 'Delhi', '2023-08-05'),
(105, 'Karan Mehta', 'Marketing', 40000.00, 'Pune', '2024-02-12'),
(106, 'Sneha Joshi', 'HR', 48000.00, 'Mumbai', '2022-11-25');

The table now contains sample employee records that can be used to practise SELECT statements.

5. Retrieving All Columns Using SELECT *

The asterisk (*) is a wildcard that tells MySQL to return all visible columns from the specified table.

Syntax

SELECT *
FROM table_name;

Example

SELECT *
FROM employees;

This query retrieves all columns and all matching rows from the employees table.

Illustrative Output

Best practice: SELECT * is useful during learning and exploration. In production queries, selecting only the required columns is often clearer and can reduce unnecessary data transfer.

6. Retrieving Specific Columns

You can retrieve only the columns needed by listing their names after SELECT. Separate multiple column names with commas.

Example 1: Retrieve Employee Names

SELECT employee_name
FROM employees;

Example 2: Retrieve Names and Salaries

SELECT employee_name, salary
FROM employees;

Example 3: Retrieve Employee Names, Departments, and Cities

SELECT employee_name, department, city
FROM employees;

These queries return only the specified columns instead of the entire table structure.

7. Using WHERE to Filter Records

The WHERE clause filters records according to a condition. Only rows that satisfy the condition are returned.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Example 1: Retrieve Employees from IT

SELECT *
FROM employees
WHERE department = 'IT';

Example 2: Retrieve Employees with a Salary Greater Than 50000

SELECT employee_name, salary
FROM employees
WHERE salary > 50000;

Example 3: Retrieve Employees from Mumbai

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai';

Use single quotes around string and date literals. Numeric values normally do not require quotes.

8. Comparison Operators in SELECT

Comparison operators help you compare column values with specified values.

Example

SELECT employee_name, salary
FROM employees
WHERE salary >= 50000;

This query returns employees whose salary is greater than or equal to 50000.

9. Using AND, OR, and NOT

Logical operators combine or reverse conditions in a WHERE clause.

9.1 AND Operator

AND returns rows only when both conditions are true.

SELECT *
FROM employees
WHERE department = 'IT'
AND salary > 50000;

This query retrieves IT employees whose salary is greater than 50000.

9.2 OR Operator

OR returns rows when at least one of the conditions is true.

SELECT *
FROM employees
WHERE city = 'Mumbai'
OR city = 'Pune';

This query retrieves employees who live in Mumbai or Pune.

9.3 NOT Operator

NOT reverses a condition.

SELECT *
FROM employees
WHERE NOT department = 'HR';

This query retrieves employees whose department is not HR. Rows where department is NULL are not returned by this condition because the comparison evaluates to UNKNOWN.

9.4 Combining Multiple Conditions

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'HR')
AND salary > 45000;

Parentheses help make the intended order of logical conditions clear.

10. Using BETWEEN

The BETWEEN operator checks whether a value falls within an inclusive range. Both boundary values are included.

Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example: Employees Earning Between 45000 and 55000

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 45000 AND 55000;

This query includes employees whose salary is exactly 45000 or 55000 as well as those between these values.

11. Using IN and NOT IN

The IN operator checks whether a value matches any value in a list. NOT IN checks whether a value does not match any value in the list.

Example 1: Retrieve Employees from Selected Cities

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Delhi');

Example 2: Exclude Selected Departments

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('HR', 'Finance');

Be careful when using NOT IN with a list or subquery that contains NULL, because NULL can affect the result of the comparison.

12. Using LIKE for Pattern Matching

The LIKE operator searches for text values that match a specified pattern.

Example 1: Names Beginning with A

SELECT *
FROM employees
WHERE employee_name LIKE 'A%';

Example 2: Names Ending with a

SELECT *
FROM employees
WHERE employee_name LIKE '%a';

Example 3: Names Containing the Text 'ha'

SELECT *
FROM employees
WHERE employee_name LIKE '%ha%';

Pattern-matching case sensitivity depends on the collation used by the column or expression.

13. Retrieving Records with NULL Values

In MySQL, NULL represents a missing or unknown value. It must be checked using IS NULL or IS NOT NULL rather than ordinary equality comparisons.

Example 1: Find Employees Without a Department

SELECT employee_name
FROM employees
WHERE department IS NULL;

Example 2: Find Employees with a Department

SELECT employee_name, department
FROM employees
WHERE department IS NOT NULL;

Do not use department = NULL to check for NULL values. Use IS NULL instead.

14. Sorting Results with ORDER BY

The ORDER BY clause sorts the result set by one or more columns.

14.1 Ascending Order

SELECT employee_name, salary
FROM employees
ORDER BY salary ASC;

ASC sorts values in ascending order. It is the default sorting direction.

14.2 Descending Order

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

DESC sorts values in descending order.

14.3 Sorting by Multiple Columns

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This query sorts by department in ascending order and then by salary in descending order within each department.

14.4 Sorting by Joining Date

SELECT employee_name, joining_date
FROM employees
ORDER BY joining_date ASC;

ORDER BY is important when a specific result order is required. Without ORDER BY, MySQL does not guarantee the order of returned rows.

15. Limiting Results with LIMIT

The LIMIT clause restricts the number of rows returned by a SELECT query.

Example 1: Retrieve the First Three Rows

SELECT *
FROM employees
LIMIT 3;

Example 2: Retrieve the Two Highest-Paid Employees

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;

Example 3: Skip Rows Using OFFSET

SELECT *
FROM employees
ORDER BY employee_id
LIMIT 2 OFFSET 2;

This query skips the first two rows in the ordered result and returns the next two rows, if available.

Important: Use ORDER BY with LIMIT when you need predictable results, such as the highest-paid employees or the first page of a report.

16. Removing Duplicate Results with DISTINCT

The DISTINCT keyword removes duplicate rows from the selected result set.

Example 1: Display Unique Departments

SELECT DISTINCT department
FROM employees;

Example 2: Display Unique Cities

SELECT DISTINCT city
FROM employees;

Example 3: Display Unique Department and City Combinations

SELECT DISTINCT department, city
FROM employees;

When multiple columns are selected, DISTINCT removes duplicate combinations of the selected column values, not duplicates in each column independently.

17. Using Column Aliases

An alias gives a column or expression a temporary name in the query result. The AS keyword can be used to define an alias.

Example 1: Rename a Column in the Output

SELECT employee_name AS Name,
       salary AS Monthly_Salary
FROM employees;

Example 2: Alias Without AS

SELECT employee_name Name
FROM employees;

Example 3: Create a Calculated Column

SELECT employee_name,
       salary,
       salary * 12 AS Annual_Salary
FROM employees;

Aliases improve readability when presenting results to users or exporting query results for reports.

18. Using Arithmetic Expressions in SELECT

SELECT can calculate values using arithmetic operators.

Example: Calculate a 10% Salary Increase

SELECT employee_name,
       salary AS Current_Salary,
       salary * 1.10 AS Salary_After_Increase
FROM employees;

This query calculates a projected salary increase. It does not update the stored salary values.

19. Using Aggregate Functions with SELECT

Aggregate functions calculate a single result from multiple rows, or a result for each group when used with GROUP BY.

Example 1: Count Employees

SELECT COUNT(*) AS Total_Employees
FROM employees;

Example 2: Calculate Total Salary

SELECT SUM(salary) AS Total_Salary
FROM employees;

Example 3: Calculate Average Salary

SELECT AVG(salary) AS Average_Salary
FROM employees;

Example 4: Find Minimum and Maximum Salaries

SELECT MIN(salary) AS Minimum_Salary,
       MAX(salary) AS Maximum_Salary
FROM employees;

COUNT(*) counts rows, including rows containing NULL values in individual columns. COUNT(column_name) counts only rows where that column is not NULL.

20. Grouping Results with GROUP BY

The GROUP BY clause groups rows with the same value in one or more columns. It is commonly used with aggregate functions to summarize data.

Example 1: Count Employees in Each Department

SELECT department,
       COUNT(*) AS Employee_Count
FROM employees
GROUP BY department;

Example 2: Calculate Average Salary by Department

SELECT department,
       AVG(salary) AS Average_Salary
FROM employees
GROUP BY department;

Example 3: Calculate Total Salary by City

SELECT city,
       SUM(salary) AS Total_Salary
FROM employees
GROUP BY city;

Each result row represents one group. If you need to filter the groups based on an aggregate result, use HAVING.

21. Filtering Groups with HAVING

The HAVING clause filters grouped results, often using aggregate functions. WHERE filters individual rows before grouping, while HAVING filters groups after grouping.

Example: Departments with More Than One Employee

SELECT department,
       COUNT(*) AS Employee_Count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Example: Departments with an Average Salary Above 45000

SELECT department,
       AVG(salary) AS Average_Salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 45000;

Use WHERE for conditions on individual records and HAVING for conditions on grouped results.

22. Retrieving Data from Multiple Tables

SELECT can retrieve related information from more than one table using joins. Joins are useful when information is distributed across related tables.

Example: Create a Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

INSERT INTO departments
(department_id, department_name)
VALUES
(1, 'IT'),
(2, 'HR'),
(3, 'Finance'),
(4, 'Marketing');

To join these tables reliably, the employees table would need a department key that matches departments.department_id. A typical relational design would store department_id in employees rather than relying only on department names.

Example: Retrieve Employees and Department Names

Assuming employees has a department_id column referencing departments.department_id:

SELECT e.employee_name,
       d.department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;

INNER JOIN returns rows with matching department IDs in both tables. Use a LEFT JOIN when you want all rows from the left table, including those without a matching department record.

23. Using SELECT Without a Table

MySQL can evaluate expressions directly in SELECT without reading a table.

Example 1: Calculate a Sum

SELECT 10 + 20 AS Total;

Example 2: Display the Current Date

SELECT CURRENT_DATE() AS Today;

Example 3: Display the Current Date and Time

SELECT NOW() AS Current_Date_Time;

These queries are useful for testing expressions, functions, and calculations.

24. Understanding the Logical Processing Order

Although a SELECT statement begins with SELECT, its clauses are logically processed in an order that helps explain how filtering and aggregation work.

1. FROM and JOIN: Identify and combine the source tables.
2. WHERE: Filter individual rows.
3. GROUP BY: Create groups of rows.
4. HAVING: Filter groups.
5. SELECT: Calculate and choose the output expressions.
6. DISTINCT: Remove duplicate result rows when specified.
7. ORDER BY: Sort the output.
8. LIMIT: Restrict the number of returned rows.

This is a simplified logical processing model. It does not necessarily describe the exact physical execution plan chosen by the MySQL optimizer.

25. Common SELECT Errors and Troubleshooting

Error 1: Unknown Column

Cause: The query refers to a column that does not exist or uses an incorrect name.

Solution: Check the table structure and use the correct column name.

DESCRIBE employees;
SELECT employee_name FROM employees;

Error 2: Table Does Not Exist

Cause: The table name is incorrect or the wrong database is selected.

Solution:

SHOW DATABASES;
USE company_db;
SHOW TABLES;

Error 3: Missing Comma Between Columns

Incorrect query:

SELECT employee_name salary
FROM employees;

This syntax can be interpreted as assigning salary as an alias to employee_name, rather than selecting both columns.

Correct query:

SELECT employee_name, salary
FROM employees;

Error 4: Incorrect Use of NULL

Incorrect query:

SELECT *
FROM employees
WHERE department = NULL;

Correct query:

SELECT *
FROM employees
WHERE department IS NULL;

Error 5: Unexpected Result Order

Cause: The query does not specify an ORDER BY clause.

Solution:

SELECT *
FROM employees
ORDER BY employee_id ASC;

Error 6: Ambiguous Column Name

Cause: Multiple joined tables contain a column with the same name.

Solution: Use table aliases to qualify column names.

SELECT e.employee_name,
       d.department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;

26. Best Practices for SELECT Queries

• Select only the columns you need instead of using SELECT * in routine application queries.
• Use WHERE to reduce the number of rows processed and returned.
• Use clear column aliases for calculated values and report headings.
• Use ORDER BY whenever a specific result order is required.
• Use LIMIT to control result size when appropriate.
• Use IS NULL and IS NOT NULL to test for NULL values.
• Use parentheses when combining complex AND and OR conditions.
• Use table aliases when joining tables with overlapping column names.
• Use GROUP BY with aggregate functions to summarize data.
• Use HAVING to filter aggregate groups.
• Check query results before relying on them for reports or business decisions.
• Use EXPLAIN to inspect how MySQL plans to execute a complex query.

27. Practical Project: Employee Data Retrieval

Project objective: Practise retrieving, filtering, sorting, and summarizing employee information using SELECT.

Task 1: Display All Employees

SELECT *
FROM employees;

Task 2: Display Names and Departments

SELECT employee_name, department
FROM employees;

Task 3: Find Employees Earning More Than 45000

SELECT employee_name, salary
FROM employees
WHERE salary > 45000;

Task 4: Find Employees from Mumbai

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai';

Task 5: Sort Employees by Salary

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Task 6: Display Unique Departments

SELECT DISTINCT department
FROM employees;

Task 7: Find the Highest Salary

SELECT MAX(salary) AS Highest_Salary
FROM employees;

Task 8: Count Employees by Department

SELECT department,
       COUNT(*) AS Employee_Count
FROM employees
GROUP BY department;

Task 9: Display the Top Three Salaries

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Task 10: Find Employees Who Joined After 2022

SELECT employee_name, joining_date
FROM employees
WHERE joining_date >= '2023-01-01'
ORDER BY joining_date;

28. Interview Questions and Answers

Q1. What is SELECT in MySQL?

Answer: SELECT is an SQL statement used to retrieve data from one or more tables or evaluate expressions.

Q2. What does SELECT * mean?

Answer: It selects all visible columns from the specified table or tables.

Q3. How can you retrieve only two columns?

Answer: Specify the required column names separated by commas, such as SELECT employee_name, salary FROM employees;.

Q4. What is the purpose of WHERE?

Answer: WHERE filters individual rows according to specified conditions.

Q5. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping, while HAVING filters groups after GROUP BY.

Q6. What is DISTINCT used for?

Answer: DISTINCT removes duplicate rows from the selected result set.

Q7. How do you sort records in descending order?

Answer: Use ORDER BY column_name DESC.

Q8. How can you retrieve only five rows?

Answer: Use LIMIT 5 at the end of the query.

Q9. How do you check for NULL values?

Answer: Use IS NULL or IS NOT NULL.

Q10. What is an alias in SELECT?

Answer: An alias is a temporary name given to a column or expression in the query output.

Q11. What does COUNT(*) do?

Answer: COUNT(*) counts the rows in the selected result or group.

Q12. Does SELECT modify table data?

Answer: A regular SELECT query retrieves data and does not modify the table records.

29. Quick Reference Cheat Sheet

30. Conclusion

The SELECT statement is the foundation of data retrieval in MySQL. It allows users to retrieve all columns or selected columns, filter records, sort results, remove duplicate rows, calculate values, and summarize information using aggregate functions. More advanced queries can combine multiple tables, grouping, and conditional logic to produce meaningful reports.

Regular practice with SELECT, WHERE, ORDER BY, LIMIT, DISTINCT, GROUP BY, and HAVING will help you develop a strong foundation in MySQL and relational database querying.

Learn MySQL with JustAcademy

Develop practical database skills with the JustAcademy MySQL Training Course.

Explore the course and register for a demo through Register for MySQL Course Demo.